--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
meshchatx/src/backend/database/schema.py 6d6b40536aa49435a195fce36c4c3e4b8d96c738 (6d6b4053) Text, 74.53 KB
T8b949e# SPDX-License-Identifier: 0BSD
Tff7b72import T7ee787re
Tff7b72from T7ee787.T7ee787provider Tff7b72import Te6edf3DatabaseProvider
Te6edf3_IDENTIFIER_RE Tff7b72= Te6edf3reTff7b72.Td2a8ffcompileTb4b4b4(Ta5d6ffrTa5d6ff"Ta5d6ff^[A-Za-z_][A-Za-z0-9_]*$Ta5d6ff"Tb4b4b4)
Tff7b72class T56d364DatabaseMigrationErrorTb4b4b4(Tf85149RuntimeErrorTb4b4b4)Tb4b4b4:
Tff7b72pass
Tff7b72class T56d364PreMigrationBackupErrorTb4b4b4(Tf85149RuntimeErrorTb4b4b4)Tb4b4b4:
Tff7b72pass
Tff7b72class T56d364PostMigrationVerificationErrorTb4b4b4(Tf85149RuntimeErrorTb4b4b4)Tb4b4b4:
Tff7b72pass
Tff7b72class T56d364DatabaseTooNewErrorTb4b4b4(Tf85149RuntimeErrorTb4b4b4)Tb4b4b4:
Tff7b72pass
Tff7b72def Td2a8ff_validate_identifierTb4b4b4(Te6edf3nameTb4b4b4: Tffa657strTb4b4b4, Te6edf3labelTb4b4b4: Tffa657str Tff7b72= Ta5d6ff"Ta5d6ffidentifierTa5d6ff"Tb4b4b4) Tff7b72-Tff7b72> Tffa657strTb4b4b4:
Tff7b72if Tff7b72not Te6edf3_IDENTIFIER_RETff7b72.Td2a8ffmatchTb4b4b4(Te6edf3nameTb4b4b4)Tb4b4b4:
Te6edf3msg Tff7b72= Ta5d6fffTa5d6ff"Ta5d6ffInvalid SQL Tffd700{Te6edf3labelTffd700}Ta5d6ff: Tffd700{Te6edf3nameTffd700!r}Ta5d6ff"
Tff7b72raise Tf85149ValueErrorTb4b4b4(Te6edf3msgTb4b4b4)
Tff7b72return Te6edf3name
Tff7b72class T56d364DatabaseSchemaTb4b4b4:
Te6edf3LATEST_VERSION Tff7b72= T79c0ff53
Tff7b72def Tff7b72__init__Tb4b4b4(Tff7b72selfTb4b4b4, Te6edf3providerTb4b4b4: Te6edf3DatabaseProviderTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffprovider Tff7b72= Te6edf3provider
Tff7b72selfTff7b72.Td2a8ff_strict_migrations Tff7b72= Tff7b72False
Tff7b72selfTff7b72.Td2a8ff_migration_errorsTb4b4b4: Tffa657listTb4b4b4[Tffa657strTb4b4b4] Tff7b72= Tb4b4b4[Tb4b4b4]
Tff7b72def Td2a8ff_safe_executeTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3queryTb4b4b4, Te6edf3paramsTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72tryTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)
Tff7b72except Tf85149Exception Tff7b72as Te6edf3eTb4b4b4:
Te6edf3err_msg Tff7b72= Tffa657strTb4b4b4(Te6edf3eTb4b4b4)Tff7b72.Td2a8fflowerTb4b4b4(Tb4b4b4)
Tff7b72if Ta5d6ff"Ta5d6ffduplicate column nameTa5d6ff" Tff7b72in Te6edf3err_msg Tff7b72or Ta5d6ff"Ta5d6ffalready existsTa5d6ff" Tff7b72in Te6edf3err_msgTb4b4b4:
Tff7b72return Tff7b72None
Tff7b72if Tff7b72selfTff7b72.Td2a8ff_strict_migrationsTb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_migration_errorsTff7b72.Td2a8ffappendTb4b4b4(Tffa657strTb4b4b4(Te6edf3eTb4b4b4)Tb4b4b4)
Tffa657printTb4b4b4(Ta5d6fffTa5d6ff"Ta5d6ffDatabase operation failed: Tffd700{Te6edf3queryTb4b4b4[Tb4b4b4:T79c0ff100Tb4b4b4]Tffd700}Ta5d6ff... Error: Tffd700{Te6edf3eTffd700}Ta5d6ff"Tb4b4b4)
Tff7b72return Tff7b72None
Tff7b72def Td2a8ffinitializeTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
T8b949e# Create core tables if they don't exist
Tff7b72selfTff7b72.Td2a8ff_create_initial_tablesTb4b4b4(Tb4b4b4)
T8b949e# Run migrations
Te6edf3current_version Tff7b72= Tff7b72selfTff7b72.Td2a8ff_get_current_versionTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffmigrateTb4b4b4(Te6edf3current_versionTb4b4b4)
Tff7b72def Td2a8ff_ensure_columnTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3table_nameTb4b4b4, Te6edf3column_nameTb4b4b4, Te6edf3column_typeTb4b4b4)Tb4b4b4:
T8b949e"""Add a column to a table if it doesn't exist."""
Te6edf3_validate_identifierTb4b4b4(Te6edf3table_nameTb4b4b4, Ta5d6ff"Ta5d6fftable nameTa5d6ff"Tb4b4b4)
Te6edf3_validate_identifierTb4b4b4(Te6edf3column_nameTb4b4b4, Ta5d6ff"Ta5d6ffcolumn nameTa5d6ff"Tb4b4b4)
Te6edf3cursor Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffconnectionTff7b72.Td2a8ffcursorTb4b4b4(Tb4b4b4)
Tff7b72tryTb4b4b4:
Te6edf3cursorTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6fffTa5d6ff"Ta5d6ffPRAGMA table_info(Tffd700{Te6edf3table_nameTffd700}Ta5d6ff)Ta5d6ff"Tb4b4b4)
Te6edf3columns Tff7b72= Tb4b4b4[Te6edf3rowTb4b4b4[T79c0ff1Tb4b4b4] Tff7b72for Te6edf3row Tff7b72in Te6edf3cursorTff7b72.Td2a8fffetchallTb4b4b4(Tb4b4b4)Tb4b4b4]
Tff7b72finallyTb4b4b4:
Te6edf3cursorTff7b72.Td2a8ffcloseTb4b4b4(Tb4b4b4)
Tff7b72if Te6edf3column_name Tff7b72not Tff7b72in Te6edf3columnsTb4b4b4:
Tff7b72tryTb4b4b4:
Te6edf3stmt_type Tff7b72= Te6edf3column_type
Te6edf3forbidden_defaults Tff7b72= Tb4b4b4[
Ta5d6ff"Ta5d6ffCURRENT_TIMESTAMPTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffCURRENT_TIMETa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffCURRENT_DATETa5d6ff"Tb4b4b4,
Tb4b4b4]
Tff7b72for Te6edf3forbidden Tff7b72in Te6edf3forbidden_defaultsTb4b4b4:
Tff7b72if Ta5d6fffTa5d6ff"Ta5d6ffDEFAULT Tffd700{Te6edf3forbiddenTffd700}Ta5d6ff" Tff7b72in Te6edf3stmt_typeTff7b72.Td2a8ffupperTb4b4b4(Tb4b4b4)Tb4b4b4:
Te6edf3stmt_type Tff7b72= Te6edf3reTff7b72.Td2a8ffsubTb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffDEFAULTTffea00\\Ta5d6ffs+Tffd700{Te6edf3forbiddenTffd700}Ta5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ff"Tb4b4b4,
Te6edf3stmt_typeTb4b4b4,
Te6edf3flagsTff7b72=Te6edf3reTff7b72.Td2a8ffIGNORECASETb4b4b4,
Tb4b4b4)Tff7b72.Td2a8ffstripTb4b4b4(Tb4b4b4)
Te6edf3res Tff7b72= Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffALTER TABLE Tffd700{Te6edf3table_nameTffd700}Ta5d6ff ADD COLUMN Tffd700{Te6edf3column_nameTffd700}Ta5d6ff Tffd700{Te6edf3stmt_typeTffd700}Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72return Te6edf3res Tff7b72is Tff7b72not Tff7b72None
Tff7b72except Tf85149Exception Tff7b72as Te6edf3eTb4b4b4:
T8b949e# Log but don't crash, we might be able to continue
Tffa657printTb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffUnexpected error adding column Tffd700{Te6edf3column_nameTffd700}Ta5d6ff to Tffd700{Te6edf3table_nameTffd700}Ta5d6ff: Tffd700{Te6edf3eTffd700}Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72return Tff7b72False
Tff7b72return Tff7b72True
Tff7b72def Td2a8ff_sync_table_columnsTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3table_nameTb4b4b4, Te6edf3create_sqlTb4b4b4)Tb4b4b4:
T8b949e"""Parse CREATE TABLE and add any missing columns to match the declaration.
Finds the column list between the first ( and last ), splits on
commas outside nested parentheses (e.g. DECIMAL(10,2)), then ensures
each column exists on the actual table.
"""
Te6edf3start_idx Tff7b72= Te6edf3create_sqlTff7b72.Td2a8fffindTb4b4b4(Ta5d6ff"Ta5d6ff(Ta5d6ff"Tb4b4b4)
Te6edf3end_idx Tff7b72= Te6edf3create_sqlTff7b72.Td2a8ffrfindTb4b4b4(Ta5d6ff"Ta5d6ff)Ta5d6ff"Tb4b4b4)
Tff7b72if Te6edf3start_idx Tff7b72== Tff7b72-T79c0ff1 Tff7b72or Te6edf3end_idx Tff7b72== Tff7b72-T79c0ff1Tb4b4b4:
Tff7b72return
Te6edf3inner_content Tff7b72= Te6edf3create_sqlTb4b4b4[Te6edf3start_idx Tff7b72+ T79c0ff1 Tb4b4b4: Te6edf3end_idxTb4b4b4]
Te6edf3definitions Tff7b72= Tb4b4b4[Tb4b4b4]
Te6edf3depth Tff7b72= T79c0ff0
Te6edf3current Tff7b72= Ta5d6ff"Ta5d6ff"
Tff7b72for Te6edf3char Tff7b72in Te6edf3inner_contentTb4b4b4:
Tff7b72if Te6edf3char Tff7b72== Ta5d6ff"Ta5d6ff(Ta5d6ff"Tb4b4b4:
Te6edf3depth Tff7b72+Tff7b72= T79c0ff1
Tff7b72elif Te6edf3char Tff7b72== Ta5d6ff"Ta5d6ff)Ta5d6ff"Tb4b4b4:
Te6edf3depth Tff7b72-Tff7b72= T79c0ff1
Tff7b72if Te6edf3char Tff7b72== Ta5d6ff"Ta5d6ff,Ta5d6ff" Tff7b72and Te6edf3depth Tff7b72== T79c0ff0Tb4b4b4:
Te6edf3definitionsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3currentTff7b72.Td2a8ffstripTb4b4b4(Tb4b4b4)Tb4b4b4)
Te6edf3current Tff7b72= Ta5d6ff"Ta5d6ff"
Tff7b72elseTb4b4b4:
Te6edf3current Tff7b72+Tff7b72= Te6edf3char
Tff7b72if Te6edf3currentTff7b72.Td2a8ffstripTb4b4b4(Tb4b4b4)Tb4b4b4:
Te6edf3definitionsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3currentTff7b72.Td2a8ffstripTb4b4b4(Tb4b4b4)Tb4b4b4)
Tff7b72for Te6edf3definition Tff7b72in Te6edf3definitionsTb4b4b4:
Te6edf3definition Tff7b72= Te6edf3definitionTff7b72.Td2a8ffstripTb4b4b4(Tb4b4b4)
T8b949e# Skip table-level constraints
Tff7b72if Tff7b72not Te6edf3definition Tff7b72or Te6edf3definitionTff7b72.Td2a8ffupperTb4b4b4(Tb4b4b4)Tff7b72.Td2a8ffstartswithTb4b4b4(
Tb4b4b4(Ta5d6ff"Ta5d6ffPRIMARY KEYTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffFOREIGN KEYTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffUNIQUETa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffCHECKTa5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)Tb4b4b4:
Tff7b72continue
Te6edf3parts Tff7b72= Te6edf3definitionTff7b72.Td2a8ffsplitTb4b4b4(Tff7b72NoneTb4b4b4, T79c0ff1Tb4b4b4)
Tff7b72if Tff7b72not Te6edf3partsTb4b4b4:
Tff7b72continue
Te6edf3column_name Tff7b72= Te6edf3partsTb4b4b4[T79c0ff0Tb4b4b4]Tff7b72.Td2a8ffstripTb4b4b4(Ta5d6ff'Ta5d6ff"Ta5d6ff'Tb4b4b4)Tff7b72.Td2a8ffstripTb4b4b4(Ta5d6ff"Ta5d6ff`Ta5d6ff"Tb4b4b4)Tff7b72.Td2a8ffstripTb4b4b4(Ta5d6ff"Ta5d6ff[Ta5d6ff"Tb4b4b4)Tff7b72.Td2a8ffstripTb4b4b4(Ta5d6ff"Ta5d6ff]Ta5d6ff"Tb4b4b4)
Te6edf3column_type Tff7b72= Te6edf3partsTb4b4b4[T79c0ff1Tb4b4b4] Tff7b72if Tffa657lenTb4b4b4(Te6edf3partsTb4b4b4) Tff7b72> T79c0ff1 Tff7b72else Ta5d6ff"Ta5d6ffTEXTTa5d6ff"
T8b949e# Special case for column types that are already PRIMARY KEY
Tff7b72if Ta5d6ff"Ta5d6ffPRIMARY KEYTa5d6ff" Tff7b72in Te6edf3column_typeTff7b72.Td2a8ffupperTb4b4b4(Tb4b4b4) Tff7b72and Te6edf3column_nameTff7b72.Td2a8ffupperTb4b4b4(Tb4b4b4) Tff7b72!= Ta5d6ff"Ta5d6ffIDTa5d6ff"Tb4b4b4:
T8b949e# We usually don't want to ALTER TABLE ADD COLUMN with PRIMARY KEY
T8b949e# unless it's the main ID which should already exist
Tff7b72continue
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Te6edf3table_nameTb4b4b4, Te6edf3column_nameTb4b4b4, Te6edf3column_typeTb4b4b4)
Tff7b72def Td2a8ffget_current_versionTb4b4b4(Tff7b72selfTb4b4b4) Tff7b72-Tff7b72> Tffa657intTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ff_get_current_versionTb4b4b4(Tb4b4b4)
Tff7b72def Td2a8ff_get_current_versionTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72tryTb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT value FROM config WHERE key = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffdatabase_versionTa5d6ff"Tb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3rowTb4b4b4:
Tff7b72return Tffa657intTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffvalueTa5d6ff"Tb4b4b4]Tb4b4b4)
Tff7b72except Tf85149Exception Tff7b72as Te6edf3eTb4b4b4:
Tffa657printTb4b4b4(Ta5d6fffTa5d6ff"Ta5d6ffFailed to get database version: Tffd700{Te6edf3eTffd700}Ta5d6ff"Tb4b4b4)
Tff7b72return T79c0ff0
Tff7b72def Td2a8ff_create_initial_tablesTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
T8b949e# We create the config table first so we can track version
Te6edf3config_sql Tff7b72= Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS config (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff key TEXT UNIQUE,
Ta5d6ff value TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Te6edf3config_sqlTb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_sync_table_columnsTb4b4b4(Ta5d6ff"Ta5d6ffconfigTa5d6ff"Tb4b4b4, Te6edf3config_sqlTb4b4b4)
T8b949e# Other essential tables that were present from version 1
T8b949e# Peewee automatically creates tables if they don't exist.
Te6edf3tables Tff7b72= Tb4b4b4{
Ta5d6ff"Ta5d6ffannouncesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS announces (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff aspect TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff identity_public_key TEXT,
Ta5d6ff app_data TEXT,
Ta5d6ff rssi INTEGER,
Ta5d6ff snr REAL,
Ta5d6ff quality REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcustom_destination_display_namesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS custom_destination_display_names (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff display_name TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fffavourite_destinationsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS favourite_destinations (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff display_name TEXT,
Ta5d6ff aspect TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_messages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff hash TEXT UNIQUE,
Ta5d6ff source_hash TEXT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff peer_hash TEXT,
Ta5d6ff state TEXT,
Ta5d6ff progress REAL,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff method TEXT,
Ta5d6ff delivery_attempts INTEGER DEFAULT 0,
Ta5d6ff next_delivery_attempt_at REAL,
Ta5d6ff title TEXT,
Ta5d6ff content TEXT,
Ta5d6ff fields TEXT,
Ta5d6ff fields_meta TEXT,
Ta5d6ff has_image INTEGER,
Ta5d6ff has_audio INTEGER,
Ta5d6ff has_files INTEGER,
Ta5d6ff has_reaction INTEGER,
Ta5d6ff has_telemetry INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff rssi INTEGER,
Ta5d6ff snr REAL,
Ta5d6ff quality REAL,
Ta5d6ff is_spam INTEGER DEFAULT 0,
Ta5d6ff reply_to_hash TEXT,
Ta5d6ff path_hops_at_send INTEGER,
Ta5d6ff path_interface_at_send TEXT,
Ta5d6ff path_finding_measure TEXT,
Ta5d6ff path_row_hash_hex TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_conversation_read_stateTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_conversation_read_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_read_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_user_iconsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_user_icons (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff icon_name TEXT,
Ta5d6ff foreground_colour TEXT,
Ta5d6ff background_colour TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffblocked_destinationsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS blocked_destinations (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffspam_keywordsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS spam_keywords (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff keyword TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffarchived_pagesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS archived_pages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff content TEXT,
Ta5d6ff hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcrawl_tasksTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS crawl_tasks (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff retry_count INTEGER DEFAULT 0,
Ta5d6ff last_retry_at DATETIME,
Ta5d6ff next_retry_at DATETIME,
Ta5d6ff status TEXT DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, page_path)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_forwarding_rulesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_rules (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff forward_to_hash TEXT,
Ta5d6ff source_filter_hash TEXT,
Ta5d6ff is_active INTEGER DEFAULT 1,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_forwarding_mappingsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_mappings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff alias_identity_private_key TEXT,
Ta5d6ff alias_hash TEXT UNIQUE,
Ta5d6ff original_sender_hash TEXT,
Ta5d6ff final_recipient_hash TEXT,
Ta5d6ff original_destination_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcall_historyTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS call_history (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff status TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffvoicemailsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS voicemails (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff filename TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff is_read INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffnotification_viewed_stateTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_viewed_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_viewed_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_telemetryTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_telemetry (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff timestamp REAL,
Ta5d6ff data BLOB,
Ta5d6ff received_from TEXT,
Ta5d6ff physical_link TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, timestamp)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fftelemetry_trackingTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS telemetry_tracking (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff is_tracking INTEGER DEFAULT 1,
Ta5d6ff interval_seconds INTEGER DEFAULT 60,
Ta5d6ff last_request_at REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffringtonesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS ringtones (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff filename TEXT,
Ta5d6ff display_name TEXT,
Ta5d6ff storage_filename TEXT,
Ta5d6ff is_primary INTEGER DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffnotification_soundsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_sounds (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff filename TEXT,
Ta5d6ff display_name TEXT,
Ta5d6ff storage_filename TEXT,
Ta5d6ff is_primary INTEGER DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcontactsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS contacts (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff remote_identity_hash TEXT UNIQUE,
Ta5d6ff lxmf_address TEXT,
Ta5d6ff lxst_address TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffnotificationsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notifications (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff type TEXT,
Ta5d6ff remote_hash TEXT,
Ta5d6ff title TEXT,
Ta5d6ff content TEXT,
Ta5d6ff is_viewed INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffkeyboard_shortcutsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS keyboard_shortcuts (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff action TEXT,
Ta5d6ff keys TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(identity_hash, action)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffmap_drawingsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS map_drawings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff name TEXT,
Ta5d6ff data TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffmap_overlay_sourcesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS map_overlay_sources (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff kind TEXT NOT NULL,
Ta5d6ff destination_hash TEXT NOT NULL,
Ta5d6ff path_or_repo_path TEXT NOT NULL,
Ta5d6ff ref TEXT NOT NULL DEFAULT Ta5d6ff'Ta5d6ffHEADTa5d6ff'Ta5d6ff,
Ta5d6ff group_name TEXT,
Ta5d6ff repository TEXT,
Ta5d6ff name TEXT NOT NULL,
Ta5d6ff enabled INTEGER NOT NULL DEFAULT 1,
Ta5d6ff visible INTEGER NOT NULL DEFAULT 1,
Ta5d6ff refresh_interval_seconds INTEGER NOT NULL DEFAULT 0,
Ta5d6ff status TEXT NOT NULL DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff last_error TEXT,
Ta5d6ff last_fetched_at DATETIME,
Ta5d6ff next_refresh_at DATETIME,
Ta5d6ff content_sha256 TEXT,
Ta5d6ff resolved_ref TEXT,
Ta5d6ff format TEXT,
Ta5d6ff byte_size INTEGER,
Ta5d6ff cache_relpath TEXT,
Ta5d6ff job_id TEXT,
Ta5d6ff generation INTEGER NOT NULL DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(identity_hash, kind, destination_hash, path_or_repo_path, ref)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS user_stickers (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff name TEXT,
Ta5d6ff image_type TEXT NOT NULL,
Ta5d6ff image_blob BLOB NOT NULL,
Ta5d6ff content_hash TEXT NOT NULL,
Ta5d6ff source_message_hash TEXT,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff updated_at REAL NOT NULL,
Ta5d6ff UNIQUE(identity_hash, content_hash)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffuser_gifsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS user_gifs (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff name TEXT,
Ta5d6ff image_type TEXT NOT NULL,
Ta5d6ff image_blob BLOB NOT NULL,
Ta5d6ff content_hash TEXT NOT NULL,
Ta5d6ff source_message_hash TEXT,
Ta5d6ff usage_count INTEGER NOT NULL DEFAULT 0,
Ta5d6ff last_used_at REAL,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff updated_at REAL NOT NULL,
Ta5d6ff UNIQUE(identity_hash, content_hash)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_last_sent_icon_hashesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_last_sent_icon_hashes (
Ta5d6ff destination_hash TEXT PRIMARY KEY,
Ta5d6ff icon_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffdebug_logsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS debug_logs (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff timestamp REAL,
Ta5d6ff level TEXT,
Ta5d6ff module TEXT,
Ta5d6ff message TEXT,
Ta5d6ff is_anomaly INTEGER DEFAULT 0,
Ta5d6ff anomaly_type TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_foldersTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_folders (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_conversation_foldersTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_conversation_folders (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff peer_hash TEXT UNIQUE,
Ta5d6ff folder_id INTEGER,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff FOREIGN KEY (folder_id) REFERENCES lxmf_folders(id) ON DELETE CASCADE
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Tb4b4b4}
Tff7b72for Te6edf3table_nameTb4b4b4, Te6edf3create_sql Tff7b72in Te6edf3tablesTff7b72.Td2a8ffitemsTb4b4b4(Tb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Te6edf3create_sqlTb4b4b4)
T8b949e# Robust self-healing: Ensure existing tables have all modern columns
Tff7b72selfTff7b72.Td2a8ff_sync_table_columnsTb4b4b4(Te6edf3table_nameTb4b4b4, Te6edf3create_sqlTb4b4b4)
T8b949e# Create indexes that were present
Tff7b72if Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffannouncesTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_aspect ON announces(aspect)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_identity_hash ON announces(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_updated_at ON announces(updated_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_source_hash ON lxmf_messages(source_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_destination_hash ON lxmf_messages(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_hash ON lxmf_messages(peer_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_timestamp ON lxmf_messages(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_ts ON lxmf_messages(peer_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_id ON lxmf_messages(peer_hash, id DESC)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_reply_to_hash ON lxmf_messages(reply_to_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffblocked_destinationsTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_blocked_destinations_hash ON blocked_destinations(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffspam_keywordsTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_spam_keywords_keyword ON spam_keywords(keyword)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffnotification_viewed_stateTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notification_viewed_state_destination_hash ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_notification_viewed_state_dest_hash_unique ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6fflxmf_telemetryTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_destination_hash ON lxmf_telemetry(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_timestamp ON lxmf_telemetry(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_telemetry_dest_ts_unique ON lxmf_telemetry(destination_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffdebug_logsTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_timestamp ON debug_logs(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_level ON debug_logs(level)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_anomaly ON debug_logs(is_anomaly)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmigrateTb4b4b4(
Tff7b72selfTb4b4b4,
Te6edf3current_versionTb4b4b4,
Te6edf3target_versionTb4b4b4: Tffa657int Tff7b72| Tff7b72None Tff7b72= Tff7b72NoneTb4b4b4,
Tff7b72*Tb4b4b4,
Te6edf3update_versionTb4b4b4: Tffa657bool Tff7b72= Tff7b72TrueTb4b4b4,
Tb4b4b4)Tb4b4b4:
Te6edf3target Tff7b72= Tb4b4b4(
Tff7b72selfTff7b72.Td2a8ffLATEST_VERSION
Tff7b72if Te6edf3target_version Tff7b72is Tff7b72None
Tff7b72else Tffa657minTb4b4b4(Tffa657maxTb4b4b4(T79c0ff0Tb4b4b4, Tffa657intTb4b4b4(Te6edf3target_versionTb4b4b4)Tb4b4b4)Tb4b4b4, Tff7b72selfTff7b72.Td2a8ffLATEST_VERSIONTb4b4b4)
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_strict_migrations Tff7b72= Tff7b72True
Tff7b72selfTff7b72.Td2a8ff_migration_errors Tff7b72= Tb4b4b4[Tb4b4b4]
Tff7b72tryTb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_run_migrationsTb4b4b4(Te6edf3current_versionTb4b4b4, Te6edf3targetTb4b4b4)
Tff7b72finallyTb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_strict_migrations Tff7b72= Tff7b72False
Tff7b72if Tff7b72selfTff7b72.Td2a8ff_migration_errorsTb4b4b4:
Te6edf3first Tff7b72= Tff7b72selfTff7b72.Td2a8ff_migration_errorsTb4b4b4[T79c0ff0Tb4b4b4]
Tff7b72raise Te6edf3DatabaseMigrationErrorTb4b4b4(
Ta5d6fffTa5d6ff"Tffd700{Tffa657lenTb4b4b4(Tff7b72selfTff7b72.Td2a8ff_migration_errorsTb4b4b4)Tffd700}Ta5d6ff migration step(s) failed: Tffd700{Te6edf3firstTffd700}Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3update_versionTb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_update_database_version_toTb4b4b4(Te6edf3targetTb4b4b4)
Tff7b72def Td2a8ffmigrate_up_toTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3versionTb4b4b4: Tffa657intTb4b4b4) Tff7b72-Tff7b72> Tff7b72NoneTb4b4b4:
T8b949e"""Apply migrations up to version (for fixtures and tests)."""
Te6edf3current Tff7b72= Tff7b72selfTff7b72.Td2a8ffget_current_versionTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffmigrateTb4b4b4(Te6edf3currentTb4b4b4, Te6edf3target_versionTff7b72=Te6edf3versionTb4b4b4)
Tff7b72def Td2a8ff_update_database_version_toTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3versionTb4b4b4: Tffa657intTb4b4b4) Tff7b72-Tff7b72> Tff7b72NoneTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO config (key, value, created_at, updated_at)
VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT(key) DO UPDATE SET
value = EXCLUDED.value,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffdatabase_versionTa5d6ff"Tb4b4b4, Tffa657strTb4b4b4(Te6edf3versionTb4b4b4)Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ff_update_database_versionTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_update_database_version_toTb4b4b4(Tff7b72selfTff7b72.Td2a8ffLATEST_VERSIONTb4b4b4)
Tff7b72def Td2a8ff_run_migrationsTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3current_versionTb4b4b4, Te6edf3target_versionTb4b4b4: Tffa657int Tff7b72| Tff7b72None Tff7b72= Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3target_version Tff7b72is Tff7b72NoneTb4b4b4:
Te6edf3target_version Tff7b72= Tff7b72selfTff7b72.Td2a8ffLATEST_VERSION
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff7 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff7Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS archived_pages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff content TEXT,
Ta5d6ff hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_destination_hash ON archived_pages(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_page_path ON archived_pages(page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_hash ON archived_pages(hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff8 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff8Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS crawl_tasks (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff retry_count INTEGER DEFAULT 0,
Ta5d6ff last_retry_at DATETIME,
Ta5d6ff next_retry_at DATETIME,
Ta5d6ff status TEXT DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_crawl_tasks_destination_hash ON crawl_tasks(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_crawl_tasks_page_path ON crawl_tasks(page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff9 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff9Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_rules (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff forward_to_hash TEXT,
Ta5d6ff source_filter_hash TEXT,
Ta5d6ff is_active INTEGER DEFAULT 1,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_rules_identity_hash ON lxmf_forwarding_rules(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_mappings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff alias_identity_private_key TEXT,
Ta5d6ff alias_hash TEXT UNIQUE,
Ta5d6ff original_sender_hash TEXT,
Ta5d6ff final_recipient_hash TEXT,
Ta5d6ff original_destination_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_alias_hash ON lxmf_forwarding_mappings(alias_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_sender_hash ON lxmf_forwarding_mappings(original_sender_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_recipient_hash ON lxmf_forwarding_mappings(final_recipient_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff10 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff10Tb4b4b4:
T8b949e# Ensure unique constraints exist for ON CONFLICT clauses
T8b949e# SQLite doesn't support adding UNIQUE constraints via ALTER TABLE,
T8b949e# but a UNIQUE index works for ON CONFLICT.
T8b949e# Clean up duplicates before adding unique indexes
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM announces WHERE id NOT IN (SELECT MAX(id) FROM announces GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM crawl_tasks WHERE id NOT IN (SELECT MAX(id) FROM crawl_tasks GROUP BY destination_hash, page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM custom_destination_display_names WHERE id NOT IN (SELECT MAX(id) FROM custom_destination_display_names GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM favourite_destinations WHERE id NOT IN (SELECT MAX(id) FROM favourite_destinations GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_user_icons WHERE id NOT IN (SELECT MAX(id) FROM lxmf_user_icons GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_conversation_read_state WHERE id NOT IN (SELECT MAX(id) FROM lxmf_conversation_read_state GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_messages WHERE id NOT IN (SELECT MAX(id) FROM lxmf_messages GROUP BY hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_announces_destination_hash_unique ON announces(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_crawl_tasks_destination_path_unique ON crawl_tasks(destination_hash, page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_custom_display_names_dest_hash_unique ON custom_destination_display_names(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_favourite_destinations_dest_hash_unique ON favourite_destinations(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_messages_hash_unique ON lxmf_messages(hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_user_icons_dest_hash_unique ON lxmf_user_icons(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_conversation_read_state_dest_hash_unique ON lxmf_conversation_read_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff11 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff11Tb4b4b4:
T8b949e# Add is_spam column to lxmf_messages if it doesn't exist
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_messages ADD COLUMN is_spam INTEGER DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff12 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff12Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS call_history (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff status TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_history_remote_hash ON call_history(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_history_timestamp ON call_history(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff13 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff13Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS voicemails (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff filename TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff is_read INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_voicemails_remote_hash ON voicemails(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_voicemails_timestamp ON voicemails(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff14 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff14Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_viewed_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_viewed_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notification_viewed_state_destination_hash ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_notification_viewed_state_dest_hash_unique ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff15 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff15Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_telemetry (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff timestamp REAL,
Ta5d6ff data BLOB,
Ta5d6ff received_from TEXT,
Ta5d6ff physical_link TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, timestamp)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_destination_hash ON lxmf_telemetry(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_timestamp ON lxmf_telemetry(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_telemetry_dest_ts_unique ON lxmf_telemetry(destination_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff16 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff16Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_forwarding_rules ADD COLUMN name TEXTTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff17 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff17Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS ringtones (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff filename TEXT,
Ta5d6ff display_name TEXT,
Ta5d6ff storage_filename TEXT,
Ta5d6ff is_primary INTEGER DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff18 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff18Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS contacts (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff remote_identity_hash TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_contacts_remote_identity_hash ON contacts(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff19 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff19Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_history_remote_name ON call_history(remote_identity_name)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff20 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff20Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notifications (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff type TEXT,
Ta5d6ff remote_hash TEXT,
Ta5d6ff title TEXT,
Ta5d6ff content TEXT,
Ta5d6ff is_viewed INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notifications_remote_hash ON notifications(remote_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notifications_timestamp ON notifications(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff21 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff21Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS keyboard_shortcuts (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff action TEXT,
Ta5d6ff keys TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(identity_hash, action)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_keyboard_shortcuts_identity_hash ON keyboard_shortcuts(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff22 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff22Tb4b4b4:
T8b949e# Optimize fetching conversations and favorites
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_timestamp ON lxmf_messages(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_favourite_destinations_aspect ON favourite_destinations(aspect)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Add index for faster searching in announces
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_updated_at ON announces(updated_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff23 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff23Tb4b4b4:
T8b949e# Further optimize conversation fetching
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_conv_optim ON lxmf_messages(source_hash, destination_hash, timestamp DESC)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Add index for unread message filtering
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_state_incoming ON lxmf_messages(state, is_incoming)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_aspect ON announces(aspect)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff24 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff24Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS call_recordings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff filename_rx TEXT,
Ta5d6ff filename_tx TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_recordings_remote_hash ON call_recordings(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_recordings_timestamp ON call_recordings(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff25 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff25Tb4b4b4:
T8b949e# Add docs_downloaded to config if not exists
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT OR IGNORE INTO config (key, value) VALUES (?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffdocs_downloadedTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ff0Ta5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff26 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff26Tb4b4b4:
T8b949e# Add initial_docs_download_attempted to config if not exists
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT OR IGNORE INTO config (key, value) VALUES (?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffinitial_docs_download_attemptedTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ff0Ta5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff28 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff28Tb4b4b4:
T8b949e# Add preferred_ringtone_id to contacts
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE contacts ADD COLUMN preferred_ringtone_id INTEGER DEFAULT NULLTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff29 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff29Tb4b4b4:
T8b949e# Performance optimization indexes
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_hash ON lxmf_messages(peer_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_timestamp ON lxmf_messages(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_ts ON lxmf_messages(peer_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_updated_at ON announces(updated_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff30 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff30Tb4b4b4:
T8b949e# Add custom_image to contacts
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE contacts ADD COLUMN custom_image TEXT DEFAULT NULLTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff31 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff31Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_last_sent_icon_hashes (
Ta5d6ff destination_hash TEXT PRIMARY KEY,
Ta5d6ff icon_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff32 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff32Tb4b4b4:
T8b949e# Add tutorial_seen and changelog_seen_version to config
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT OR IGNORE INTO config (key, value) VALUES (?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6fftutorial_seenTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6fffalseTa5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT OR IGNORE INTO config (key, value) VALUES (?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffchangelog_seen_versionTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ff0.0.0Ta5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff33 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff33Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS debug_logs (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff timestamp REAL,
Ta5d6ff level TEXT,
Ta5d6ff module TEXT,
Ta5d6ff message TEXT,
Ta5d6ff is_anomaly INTEGER DEFAULT 0,
Ta5d6ff anomaly_type TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_timestamp ON debug_logs(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_level ON debug_logs(level)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_debug_logs_anomaly ON debug_logs(is_anomaly)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff34 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff34Tb4b4b4:
T8b949e# Add updated_at to crawl_tasks
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE crawl_tasks ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMPTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff35 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff35Tb4b4b4:
T8b949e# Add lxmf_address and lxst_address to contacts
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE contacts ADD COLUMN lxmf_address TEXT DEFAULT NULLTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE contacts ADD COLUMN lxst_address TEXT DEFAULT NULLTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff36 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff36Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_folders (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_conversation_folders (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff peer_hash TEXT UNIQUE,
Ta5d6ff folder_id INTEGER,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff FOREIGN KEY (folder_id) REFERENCES lxmf_folders(id) ON DELETE CASCADE
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_conversation_folders_peer_hash ON lxmf_conversation_folders(peer_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_conversation_folders_folder_id ON lxmf_conversation_folders(folder_id)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff37 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff37Tb4b4b4:
T8b949e# Add is_telemetry_trusted to contacts
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE contacts ADD COLUMN is_telemetry_trusted INTEGER DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Ensure telemetry_enabled exists in config and is false by default
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffINSERT OR IGNORE INTO config (key, value) VALUES (?, ?)Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6fftelemetry_enabledTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6fffalseTa5d6ff"Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff38 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff38Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_messages ADD COLUMN reply_to_hash TEXTTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_reply_to_hash ON lxmf_messages(reply_to_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff39 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff39Tb4b4b4:
T8b949e# Indexes for contacts JOIN columns (used in message_handler.get_conversations
T8b949e# and announce_manager.get_filtered_announces OR-based JOINs)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_contacts_lxmf_address ON contacts(lxmf_address)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_contacts_lxst_address ON contacts(lxst_address)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Notifications: filter by is_viewed
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notifications_is_viewed ON notifications(is_viewed)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Map drawings: lookup by identity_hash
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_map_drawings_identity_hash ON map_drawings(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_map_drawings_identity_name ON map_drawings(identity_hash, name)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Voicemails: filter by is_read
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_voicemails_is_read ON voicemails(is_read)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Archived pages: ORDER BY created_at for cleanup queries
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_created_at ON archived_pages(created_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Conversation message state+peer: for failed_count subquery
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_state_peer ON lxmf_messages(state, peer_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff40 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff40Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS crash_history (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff timestamp REAL,
Ta5d6ff error_type TEXT,
Ta5d6ff error_message TEXT,
Ta5d6ff diagnosed_cause TEXT,
Ta5d6ff symptoms TEXT,
Ta5d6ff probability INTEGER,
Ta5d6ff entropy REAL,
Ta5d6ff divergence REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_crash_history_timestamp ON crash_history(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff41 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff41Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_messages ADD COLUMN attachments_stripped INTEGER DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff42 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff42Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS access_attempts (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff client_ip TEXT NOT NULL,
Ta5d6ff user_agent TEXT,
Ta5d6ff user_agent_hash TEXT NOT NULL,
Ta5d6ff path TEXT NOT NULL,
Ta5d6ff method TEXT NOT NULL,
Ta5d6ff outcome TEXT NOT NULL,
Ta5d6ff detail TEXT
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_access_attempts_created ON access_attempts(created_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_access_attempts_ip_time ON access_attempts(client_ip, created_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_access_attempts_identity_time ON access_attempts(identity_hash, created_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS trusted_login_clients (
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff client_ip TEXT NOT NULL,
Ta5d6ff user_agent_hash TEXT NOT NULL,
Ta5d6ff last_success_at REAL NOT NULL,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff PRIMARY KEY (identity_hash, client_ip, user_agent_hash)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_trusted_login_identity ON trusted_login_clients(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff43 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff43Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_conversation_pins (
Ta5d6ff peer_hash TEXT PRIMARY KEY NOT NULL,
Ta5d6ff pinned_at INTEGER NOT NULL
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_conversation_pins_pinned_at ON lxmf_conversation_pins(pinned_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff44 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff44Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS user_stickers (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff name TEXT,
Ta5d6ff image_type TEXT NOT NULL,
Ta5d6ff image_blob BLOB NOT NULL,
Ta5d6ff content_hash TEXT NOT NULL,
Ta5d6ff source_message_hash TEXT,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff updated_at REAL NOT NULL,
Ta5d6ff UNIQUE(identity_hash, content_hash)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_stickers_identity ON user_stickers(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_stickers_identity_updated ON user_stickers(identity_hash, updated_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff45 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff45Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS user_gifs (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff name TEXT,
Ta5d6ff image_type TEXT NOT NULL,
Ta5d6ff image_blob BLOB NOT NULL,
Ta5d6ff content_hash TEXT NOT NULL,
Ta5d6ff source_message_hash TEXT,
Ta5d6ff usage_count INTEGER NOT NULL DEFAULT 0,
Ta5d6ff last_used_at REAL,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff updated_at REAL NOT NULL,
Ta5d6ff UNIQUE(identity_hash, content_hash)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_gifs_identity ON user_gifs(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_gifs_identity_usage ON user_gifs(identity_hash, usage_count, last_used_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff46 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff46Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS user_sticker_packs (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff title TEXT NOT NULL,
Ta5d6ff short_name TEXT,
Ta5d6ff description TEXT,
Ta5d6ff pack_type TEXT NOT NULL DEFAULT Ta5d6ff'Ta5d6ffmixedTa5d6ff'Ta5d6ff,
Ta5d6ff author TEXT,
Ta5d6ff is_strict INTEGER NOT NULL DEFAULT 1,
Ta5d6ff cover_sticker_id INTEGER,
Ta5d6ff sort_order INTEGER NOT NULL DEFAULT 0,
Ta5d6ff created_at REAL NOT NULL,
Ta5d6ff updated_at REAL NOT NULL
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_sticker_packs_identity ON user_sticker_packs(identity_hash, sort_order)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_user_sticker_packs_short_name ON user_sticker_packs(identity_hash, short_name) WHERE short_name IS NOT NULLTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffpack_idTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffemojiTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffTEXTTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffwidthTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffheightTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffduration_msTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6fffpsTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffREALTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(
Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffis_animatedTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffINTEGER NOT NULL DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(
Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffis_videoTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffINTEGER NOT NULL DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(
Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffis_strictTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffINTEGER NOT NULL DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(
Ta5d6ff"Ta5d6ffuser_stickersTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffsort_orderTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffINTEGER NOT NULL DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_user_stickers_pack ON user_stickers(pack_id, sort_order)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff47 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff47Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffpath_hops_at_sendTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffpath_interface_at_sendTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffTEXTTa5d6ff"Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff48 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff48Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffpath_finding_measureTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffTEXTTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffpath_row_hash_hexTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffTEXTTa5d6ff"Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff49 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff49Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_sounds (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff filename TEXT,
Ta5d6ff display_name TEXT,
Ta5d6ff storage_filename TEXT,
Ta5d6ff is_primary INTEGER DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff50 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff50Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS map_overlay_sources (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff identity_hash TEXT NOT NULL,
Ta5d6ff kind TEXT NOT NULL,
Ta5d6ff destination_hash TEXT NOT NULL,
Ta5d6ff path_or_repo_path TEXT NOT NULL,
Ta5d6ff ref TEXT NOT NULL DEFAULT Ta5d6ff'Ta5d6ffHEADTa5d6ff'Ta5d6ff,
Ta5d6ff group_name TEXT,
Ta5d6ff repository TEXT,
Ta5d6ff name TEXT NOT NULL,
Ta5d6ff enabled INTEGER NOT NULL DEFAULT 1,
Ta5d6ff visible INTEGER NOT NULL DEFAULT 1,
Ta5d6ff refresh_interval_seconds INTEGER NOT NULL DEFAULT 0,
Ta5d6ff status TEXT NOT NULL DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff last_error TEXT,
Ta5d6ff last_fetched_at DATETIME,
Ta5d6ff next_refresh_at DATETIME,
Ta5d6ff content_sha256 TEXT,
Ta5d6ff resolved_ref TEXT,
Ta5d6ff format TEXT,
Ta5d6ff byte_size INTEGER,
Ta5d6ff cache_relpath TEXT,
Ta5d6ff job_id TEXT,
Ta5d6ff generation INTEGER NOT NULL DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(identity_hash, kind, destination_hash, path_or_repo_path, ref)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_map_overlay_sources_identity Ta5d6ff"
Ta5d6ff"Ta5d6ffON map_overlay_sources(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_map_overlay_sources_refresh Ta5d6ff"
Ta5d6ff"Ta5d6ffON map_overlay_sources(enabled, next_refresh_at)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff51 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff51Tb4b4b4:
T8b949e# Slim conversation list/thread loads: persist attachment flags and
T8b949e# fields_meta (fields without base64 payloads) so queries never need
T8b949e# to instr()/SELECT multi-MB fields blobs on the hot path.
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6fffields_metaTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffTEXTTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffhas_imageTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffhas_audioTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffhas_filesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffhas_reactionTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_ensure_columnTb4b4b4(Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffhas_telemetryTa5d6ff"Tb4b4b4, Ta5d6ff"Ta5d6ffINTEGERTa5d6ff"Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_id Ta5d6ff"
Ta5d6ff"Ta5d6ffON lxmf_messages(peer_hash, id DESC)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
T8b949e# Fresh installs have an empty messages table. Skip backfill UPDATEs
T8b949e# so Database Initialization stays fast. Upsert fills flags going forward.
Te6edf3has_messages Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT 1 AS ok FROM lxmf_messages LIMIT 1Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3has_messagesTb4b4b4:
T8b949e# Backfill flags for the latest message of each conversation only.
T8b949e# That is the row the sidebar query reads. Full-table instr would be
T8b949e# too expensive on large histories.
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
UPDATE lxmf_messages SET
has_image = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
THEN 1 ELSE 0 END,
has_audio = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
THEN 1 ELSE 0 END,
has_files = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"file_attachments"') > 0
OR instr(fields, '"0x07"') > 0)
THEN 1 ELSE 0 END,
has_reaction = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
THEN 1 ELSE 0 END,
has_telemetry = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"telemetry"') > 0
OR instr(fields, '"0x08"') > 0
OR instr(fields, '"telemetry_stream"') > 0)
THEN 1 ELSE 0 END
WHERE id IN (
SELECT MAX(id) FROM lxmf_messages
WHERE peer_hash IS NOT NULL
GROUP BY peer_hash
)
"""Tb4b4b4,
Tb4b4b4)
T8b949e# Also flag oversized fields rows (almost always attachments). This keeps
T8b949e# heavy conversation opens from SELECT-ing multi-MB blobs without meta.
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
UPDATE lxmf_messages SET
has_image = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"image"') > 0 OR instr(fields, '"0x05"') > 0)
THEN 1 ELSE 0 END,
has_audio = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"audio"') > 0 OR instr(fields, '"0x06"') > 0)
THEN 1 ELSE 0 END,
has_files = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"file_attachments"') > 0
OR instr(fields, '"0x07"') > 0)
THEN 1 ELSE 0 END,
has_reaction = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"reaction"') > 0 OR instr(fields, '"0x40"') > 0)
THEN 1 ELSE 0 END,
has_telemetry = CASE
WHEN fields IS NOT NULL AND fields != '' AND fields != '{}'
AND (instr(fields, '"telemetry"') > 0
OR instr(fields, '"0x08"') > 0
OR instr(fields, '"telemetry_stream"') > 0)
THEN 1 ELSE 0 END
WHERE has_image IS NULL
AND length(COALESCE(fields, '')) > 16384
"""Tb4b4b4,
Tb4b4b4)
T8b949e# Small fields can be copied as-is into fields_meta for thread loads.
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
UPDATE lxmf_messages
SET fields_meta = fields
WHERE (fields_meta IS NULL OR fields_meta = '')
AND fields IS NOT NULL AND fields != ''
AND length(fields) <= 16384
"""Tb4b4b4,
Tb4b4b4)
T8b949e# Large attachment blobs: store a tiny synthetic meta from flags.
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
UPDATE lxmf_messages
SET fields_meta = CASE
WHEN COALESCE(has_image, 0) = 1 THEN
'{"image":{"image_type":"png","image_size":0,"image_bytes":null}}'
WHEN COALESCE(has_audio, 0) = 1 THEN
'{"audio":{"audio_mode":0,"audio_size":0,"audio_bytes":null}}'
WHEN COALESCE(has_files, 0) = 1 THEN
'{"file_attachments":[{"file_name":"attachment","file_size":0,"file_bytes":null}]}'
WHEN COALESCE(has_reaction, 0) = 1 THEN
'{"reaction":{}}'
WHEN COALESCE(has_telemetry, 0) = 1 THEN
'{"telemetry":{}}'
ELSE '{}'
END
WHERE (fields_meta IS NULL OR fields_meta = '')
AND fields IS NOT NULL AND length(fields) > 16384
"""Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff52 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff52Tb4b4b4:
T8b949e# Materialized per-peer latest row so conversation list queries do not
T8b949e# GROUP BY the full lxmf_messages table on every refresh.
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
CREATE TABLE IF NOT EXISTS lxmf_conversation_summaries (
peer_hash TEXT PRIMARY KEY NOT NULL,
latest_message_id INTEGER NOT NULL,
latest_message_hash TEXT,
source_hash TEXT,
destination_hash TEXT,
state TEXT,
progress REAL,
is_incoming INTEGER,
title TEXT,
content_preview TEXT,
timestamp REAL,
is_spam INTEGER,
reply_to_hash TEXT,
created_at TEXT,
updated_at TEXT,
has_image INTEGER,
has_audio INTEGER,
has_files INTEGER,
has_reaction INTEGER,
has_telemetry INTEGER,
failed_count INTEGER NOT NULL DEFAULT 0
)
"""Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_conversation_summaries_latest_id Ta5d6ff"
Ta5d6ff"Ta5d6ffON lxmf_conversation_summaries(latest_message_id DESC)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_conversation_summaries_timestamp Ta5d6ff"
Ta5d6ff"Ta5d6ffON lxmf_conversation_summaries(timestamp DESC)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Te6edf3has_messages Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT 1 AS ok FROM lxmf_messages LIMIT 1Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3has_messagesTb4b4b4:
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
INSERT OR REPLACE INTO lxmf_conversation_summaries (
peer_hash, latest_message_id, latest_message_hash,
source_hash, destination_hash, state, progress, is_incoming,
title, content_preview, timestamp, is_spam, reply_to_hash,
created_at, updated_at,
has_image, has_audio, has_files, has_reaction, has_telemetry,
failed_count
)
SELECT
m.peer_hash,
m.id,
m.hash,
m.source_hash,
m.destination_hash,
m.state,
m.progress,
m.is_incoming,
m.title,
substr(COALESCE(m.content, ''), 1, 240),
m.timestamp,
m.is_spam,
m.reply_to_hash,
m.created_at,
m.updated_at,
COALESCE(m.has_image, 0),
COALESCE(m.has_audio, 0),
COALESCE(m.has_files, 0),
COALESCE(m.has_reaction, 0),
COALESCE(m.has_telemetry, 0),
COALESCE(fc.failed_count, 0)
FROM lxmf_messages m
INNER JOIN (
SELECT peer_hash, MAX(id) AS max_id
FROM lxmf_messages
WHERE peer_hash IS NOT NULL
GROUP BY peer_hash
) latest ON latest.peer_hash = m.peer_hash AND latest.max_id = m.id
LEFT JOIN (
SELECT peer_hash, COUNT(*) AS failed_count
FROM lxmf_messages
WHERE state = 'failed'
GROUP BY peer_hash
) fc ON fc.peer_hash = m.peer_hash
"""Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff53 Tff7b72and Te6edf3target_version Tff7b72>Tff7b72= T79c0ff53Tb4b4b4:
T8b949e# Encrypted client-side RRC room keys for +k rooms (identity-scoped).
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
T8b949e"""
CREATE TABLE IF NOT EXISTS rrc_room_keys (
hub_hash TEXT NOT NULL,
dest_name TEXT NOT NULL,
room TEXT NOT NULL,
nonce BLOB NOT NULL,
ciphertext BLOB NOT NULL,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (hub_hash, dest_name, room)
)
"""Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ff_safe_executeTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_rrc_room_keys_hub Ta5d6ff"
Ta5d6ff"Ta5d6ffON rrc_room_keys(hub_hash, dest_name)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────